package com.youyu.core.common.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.yx.bean.Bean;
import org.yx.log.Log;
import org.yx.util.StringUtils;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

/**
 * <p>Author:  dengxj</p>
 * <p>Date: 2016/7/7 16:48</p>
 * <p>Description:文件转excel的manager实现类</p>
 * <p>Copyright: Copyright (c) 2016</p>
 * <p>Company: yihu.com</p>
 */
@Bean
public class FileToExcelManagerImpl implements FileToExcelManager {

    private CellStyle styleTitle;
    private CellStyle styleColumn;
    private CellStyle styleContentText;
    private CellStyle styleContentBlank;
    private CellStyle styleContentDate;
    private CellStyle styleContentDatetime;
    private CellStyle styleContentNumeric;
    private CellStyle styleContentInteger;

    /**
     * 初始化样式
     */
    private void initStyle(Workbook wb, CellStyle styleTitle,
                           CellStyle styleColumn, CellStyle styleContentText, CellStyle styleContentDate,
                           CellStyle styleContentDatetime, CellStyle styleContentNumeric, CellStyle styleContentInteger
    ) {
        DataFormat format = wb.createDataFormat();

        // 标题字体
        Font fontTitle = wb.createFont();
        fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontTitle.setFontHeightInPoints((short) 16);
        // 标题样式
        styleTitle.setFont(fontTitle);
        styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        short dataformat = format.getFormat("text");
        styleTitle.setDataFormat(dataformat);
        // 表头字体
        Font fontCulumn = wb.createFont();
        // fontCulumn.setFontName("宋体");
        // fontCulumn.setFontHeightInPoints( (short) 9);
        fontCulumn.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 表头样式
        styleColumn.setFont(fontCulumn);
        styleColumn.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleColumn.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleColumn.setDataFormat(dataformat);
        // 内容字体
        Font fontContent = wb.createFont();
        // fontContent.setFontName("宋体");
        // fontContent.setFontHeightInPoints( (short) 12);
        // 内容样式
        // 文本
        styleContentText.setFont(fontContent);
        styleContentText.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleContentText.setHidden(true);
        styleContentText.setWrapText(true);
        styleContentText.setDataFormat(dataformat);
        // 日期
        styleContentDate.setFont(fontContent);
        styleContentDate.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleContentDate.setHidden(true);
        styleContentDate.setWrapText(true);
        styleContentDate.setDataFormat(format.getFormat("yyyy-m-d"));
        // 日期+时间
        styleContentDatetime.setFont(fontContent);
        styleContentDatetime.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleContentDatetime.setHidden(true);
        styleContentDatetime.setWrapText(true);
        styleContentDatetime.setDataFormat(format.getFormat("yyyy-m-d H:mm:ss"));
        // 小数
        styleContentNumeric.setFont(fontContent);
        styleContentNumeric.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleContentNumeric.setHidden(true);
        styleContentNumeric.setWrapText(true);
        styleContentNumeric.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        // 整数
        styleContentInteger.setFont(fontContent);
        styleContentInteger.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styleContentInteger.setHidden(true);
        styleContentInteger.setWrapText(true);
        styleContentInteger.setDataFormat(format.getFormat("0"));
    }

    /**
     * 保存Excel表格
     *
     * @param filename
     * @param dataRecords
     */
    public void save(String filename, DataRecord... dataRecords) throws Exception{
        if (dataRecords == null || dataRecords.length == 0) {
            throw new IllegalArgumentException("不能创建工作表!");
        }

        Workbook wb;
        if(filename.endsWith(".xlsx")){
            wb = new XSSFWorkbook();
        }else{
            wb = new HSSFWorkbook();
            if(filename.indexOf(".xls") == -1){
                filename += ".xls";
            }
        }
        wb = doSheets(wb,dataRecords);
        OutputStream out_ = null;
        try {
            //创建文件输出流
            out_ = new FileOutputStream(System.getProperty("user.dir")
                    + "\\" + filename);
            wb.write(out_);
            out_.flush();
            out_.close();
        } catch (Exception e) {
            Log.get(FileToExcelManagerImpl.class).error(e.getMessage());
        } finally{
            wb = null;
            if(null != out_) {
                out_.close();
            }
        }

    }

    /**
     * 处理单个sheet
     * @param sheet
     * @param dataRecord
     */
    private void doSheet(Sheet sheet,DataRecord dataRecord){
        if(sheet instanceof HSSFSheet){
            HSSFSheet s = (HSSFSheet) sheet;
            // 打印网格线
            s.setGridsPrinted(true);
        }
        // 水平居中
        sheet.setHorizontallyCenter(true);
        sheet.setAutobreaks(true);

        String[] columnName = dataRecord.getColumnName();
        int colunmLength = 0;
        if (columnName != null) {
            colunmLength = columnName.length;
        }
        int rowNumber = 0;
        Row row = sheet.createRow(rowNumber++);
        row.setHeightInPoints(25);
        Cell cell = row.createCell(0);
        if (colunmLength > 0) {
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colunmLength - 1)));
        }
        cell.setCellValue(dataRecord.getTitle());
        cell.setCellStyle(styleTitle);

        if(dataRecord.getSubTitle() != null){
            row = sheet.createRow(rowNumber++);
            row.setHeightInPoints(25);
            cell = row.createCell(0);
            if (colunmLength > 0) {
                sheet.addMergedRegion(new CellRangeAddress(rowNumber - 1, rowNumber - 1, 0, (colunmLength - 1)));
            }
            cell.setCellValue(dataRecord.getSubTitle());
            cell.setCellStyle(styleContentText);
        }
        // 表头
        boolean isAllNull = true;
        //标识表头是否全为NULL
        for (int c = 0; c < colunmLength; c++) {
            if(StringUtils.isNotEmpty(columnName[c])){
                isAllNull = false;
                break;
            }
        }
        if(!isAllNull){
            //如果表头全为NULL，则不输出表头
            row = sheet.createRow(rowNumber++);
            row.setHeightInPoints(20);
            for (int c = 0; c < colunmLength; c++) {
                cell = row.createCell( c);
                cell.setCellValue(columnName[c]);
                cell.setCellStyle(styleColumn);
                cell.setAsActiveCell();
            }
        }
        doSheetContent(sheet, dataRecord, rowNumber);
    }

    /**
     * 处理单个sheet的数据内容
     * @param sheet
     * @param dataRecord
     * @param rowNumber
     */
    private void doSheetContent(Sheet sheet,DataRecord dataRecord,int rowNumber){
        // 列宽度
        short[] columnWith = dataRecord.getColumnWith();
        if(columnWith != null){
            for (int j = 0; j < columnWith.length; j++) {
                sheet.setColumnWidth(j, columnWith[j] * 200);
            }
        }
        // 内容
        for (int index = 0; index < dataRecord.getRow().length; index++) {
            // 多行数据
            Row row = sheet.createRow(rowNumber++);
            row.setHeightInPoints(20);
            DataRow dataRow = dataRecord.getRow()[index];
            if(dataRow ==null) continue;
            DataCell[] dataCells = dataRow.getCell();

            for (int col = 0; col < dataCells.length; col++) {
                // 一行数据多单元格
                Cell cell = row.createCell(col);
                DataCell dataCell = dataCells[col];
                if(dataCell == null){
                    continue ;
                }
                switch (dataCell.getType()) {
                    case DataCell.DATA_TYPE_TEXT:
                        cell.setCellStyle(styleContentText);
                        cell.setCellValue(dataCell.getContent());
                        break;
                    case DataCell.DATA_TYPE_BLANK:
                        cell.setCellStyle(styleContentBlank);
                        cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                        cell.setCellValue(dataCell.getContent());
                        break;
                    case DataCell.DATA_TYPE_DATE:
                        cell.setCellStyle(styleContentDate);
                        cell.setCellValue(dataCell.getContent());
                        break;
                    case DataCell.DATA_TYPE_DATETIME:
                        cell.setCellStyle(styleContentDatetime);
                        cell.setCellValue(dataCell.getContent());
                        break;
                    case DataCell.DATA_TYPE_NUMERIC:
                        cell.setCellStyle(styleContentNumeric);
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        if(StringUtils.isEmpty(dataCell.getContent())){
                            cell.setCellValue("");
                        }else{
                            try{
                                cell.setCellValue(Double.parseDouble(dataCell.getContent()));
                            } catch (NumberFormatException e) {
                                cell.setCellValue(dataCell.getContent());
                            }
                        }

                        break;
                    case DataCell.DATA_TYPE_INTEGER:
                        cell.setCellStyle(styleContentInteger);
                        if(StringUtils.isEmpty(dataCell.getContent())){
                            cell.setCellValue("");
                        }else{
                            try{
                                cell.setCellValue(Long.parseLong(dataCell.getContent()));
                            } catch (NumberFormatException e) {
                                cell.setCellValue(dataCell.getContent());
                            }
                        }
                        break;
                    default:
                        cell.setCellStyle(styleContentText);
                        cell.setCellValue(dataCell.getContent());
                        break;
                }
            }
            // 打印属性
            PrintSetup print = sheet.getPrintSetup();
            sheet.setAutobreaks(true);
            print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
            print.setFitHeight((short) 1);
            print.setFitWidth((short) 1);
        }
    }

    /**
     * 处理单个sheet（两行表头）
     * @param sheet
     * @param dataRecord
     */
    private void doCrossSheet(Sheet sheet,DataRecord dataRecord){
        if(sheet instanceof HSSFSheet){
            HSSFSheet s = (HSSFSheet) sheet;
            // 打印网格线
            s.setGridsPrinted(true);
        }
        // 水平居中
        sheet.setHorizontallyCenter(true);
        sheet.setAutobreaks(true);

        int colunmLength = 0;
        for(DataCell dataCell:dataRecord.getDataHeadList().get(0)){
            colunmLength += dataCell.getColSpan();
        }
        int rowNumber = 0;
        Row row = sheet.createRow(rowNumber++);
        row.setHeightInPoints(25);
        Cell cell = row.createCell(0);
        if (colunmLength > 0) {
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colunmLength - 1)));
        }
        cell.setCellValue(dataRecord.getTitle());
        cell.setCellStyle(styleTitle);

        if(dataRecord.getSubTitle() != null){
            row = sheet.createRow(rowNumber++);
            row.setHeightInPoints(25);
            cell = row.createCell(0);
            if (colunmLength > 0) {
                sheet.addMergedRegion(new CellRangeAddress(rowNumber - 1, rowNumber - 1, 0, (colunmLength - 1)));
            }
            cell.setCellValue(dataRecord.getSubTitle());
            cell.setCellStyle(styleContentText);
        }
        // 两行表头
        List<DataCell> dataCellsFirst = dataRecord.getDataHeadList().get(0);
        List<DataCell> dataCellsSecond = dataRecord.getDataHeadList().get(1);
        int dataCellsSecondColIndex = 0;
        row = sheet.createRow(rowNumber);
        row.setHeightInPoints(20);
        int columnIndex = 0;
        for (DataCell dataCell:dataCellsFirst) {
            cell = row.createCell(columnIndex);
            if(dataCell.getRowSpan()>1){
                sheet.addMergedRegion(new CellRangeAddress(rowNumber, (rowNumber+dataCell.getRowSpan() -1), columnIndex, columnIndex));
                dataCellsSecondColIndex++;
            }
            if(dataCell.getColSpan()>1){
                sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber,columnIndex,  (columnIndex+dataCell.getColSpan() -1)));
                columnIndex+=dataCell.getColSpan();
            }else{
                columnIndex++;
            }
            cell.setCellValue(dataCell.getContent());
            cell.setCellStyle(styleColumn);
            cell.setAsActiveCell();
        }
        row = sheet.createRow(++rowNumber);
        row.setHeightInPoints(20);
        columnIndex = dataCellsSecondColIndex;
        for (DataCell dataCell:dataCellsSecond) {
            cell = row.createCell(columnIndex);
            if(dataCell.getRowSpan()>1){
                sheet.addMergedRegion(new CellRangeAddress(rowNumber, (rowNumber+dataCell.getRowSpan() -1), columnIndex, columnIndex));
            }
            if(dataCell.getColSpan()>1){
                sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber,columnIndex,  (columnIndex+dataCell.getColSpan() -1)));
                columnIndex+=dataCell.getColSpan();
            }else{
                columnIndex++;
            }
            cell.setCellValue(dataCell.getContent());
            cell.setCellStyle(styleColumn);
            cell.setAsActiveCell();
        }
        rowNumber++;
        doSheetContent(sheet,dataRecord,rowNumber);
    }

    /**
     * 创建一个或多个sheet
     *
     * @param dataRecords
     */
    private Workbook doSheets(Workbook wb,DataRecord... dataRecords) throws Exception {
        int sheetNum = dataRecords.length;
        if (sheetNum == 0) {
            throw new Exception("因没有提供数据，故不能创建工作表!");
        }
        Sheet[] sheets = new Sheet[sheetNum];
        DataRecord dataRecord = null;
        styleTitle = wb.createCellStyle();
        styleColumn = wb.createCellStyle();
        styleContentText = wb.createCellStyle();
        styleContentBlank = wb.createCellStyle();
        styleContentDate = wb.createCellStyle();
        styleContentDatetime = wb.createCellStyle();
        styleContentNumeric = wb.createCellStyle();
        styleContentInteger = wb.createCellStyle();
        this.initStyle(wb, styleTitle, styleColumn, styleContentText, styleContentDate, styleContentDatetime, styleContentNumeric, styleContentInteger);
        for (int i = 0; i < sheetNum; i++) {
            // 多个sheet
            dataRecord = dataRecords[i];
            if (dataRecord == null) {
                throw new Exception("DataRecord is disabled");
            }
            sheets[i] = wb.createSheet();
            Sheet sheet = sheets[i];
            // 设置表的名字
            wb.setSheetName(i, dataRecord.getSheetName());
            if(dataRecord.getDataHeadList() == null || dataRecord.getDataHeadList().size() == 0){
                doSheet(sheet,dataRecord);
            }else{
                doCrossSheet(sheet,dataRecord);
            }
        }
        return wb;
    }

}